package com.bluegalaxy.nhb.snservice.service.execute.impl;

import com.bluegalaxy.nhb.snservice.common.config.MysqlParamConfig;
import com.bluegalaxy.nhb.snservice.common.utils.SqlTableUtil;
import com.bluegalaxy.nhb.snservice.common.utils.StringUtil;
import com.bluegalaxy.nhb.snservice.domain.enums.UsersFieldEnum;
import com.bluegalaxy.nhb.snservice.domain.props.CkDbBaseProperties;
import com.bluegalaxy.nhb.snservice.service.execute.ExecuteService;
import com.bluegalaxy.nhb.snservice.service.execute.XxService;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import org.springframework.util.FileCopyUtils;

import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author:
 * @date: 2021/6/23 12:59
 * @description:
 */
@Service("XxService")
public class XxServiceImpl implements XxService {

    private static Log log = LogFactory.getLog(XxServiceImpl.class);

    @Autowired
    private MysqlParamConfig paramConfig;

    @Override
    public List<Map<String,Object>> selectUsers(){
        List<Map<String,Object>> list = new ArrayList<>();
        Connection conn = null;
        Statement stat = null;

        String url = paramConfig.getUrl();
        String username = paramConfig.getUsername();
        String password = paramConfig.getPassword();

        try {
            //连接数据库
            Class.forName(paramConfig.getDriverClassName());
            //测试url中是否包含useSSL字段，没有则添加设该字段且禁用
            conn = DriverManager.getConnection(url, username, password);
            stat = conn.createStatement();

            ClassPathResource classPathResource = new ClassPathResource("sql/users_select_mysql.sql");

            InputStream inputStream =classPathResource.getInputStream();
            byte[] bdata = FileCopyUtils.copyToByteArray(inputStream);
            String querySql = new String(bdata, StandardCharsets.UTF_8);

            ResultSet rs =  stat.executeQuery(querySql);

            log.info("ResultSet:"+rs);
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while (rs.next()){
                Map<String, Object> rowData = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(UsersFieldEnum.getValue(md.getColumnName(i)), rs.getObject(i)==null?"":rs.getObject(i));
                }
                list.add(rowData);
            }
        } catch (IOException | SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            try {
                if (stat != null){
                    stat.close();
                }
                if (conn != null){
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

        return list;
    }

    @Override
    public List<Map<String,Object>> selectUserAttribute(){
        List<Map<String,Object>> list = new ArrayList<>();
        Connection conn = null;
        Statement stat = null;

        String url = paramConfig.getUrl();
        String username = paramConfig.getUsername();
        String password = paramConfig.getPassword();

        try {
            //连接数据库
            Class.forName(paramConfig.getDriverClassName());
            //测试url中是否包含useSSL字段，没有则添加设该字段且禁用
            conn = DriverManager.getConnection(url, username, password);
            stat = conn.createStatement();

            ClassPathResource classPathResource = new ClassPathResource("sql/users_attr_select_mysql.sql");

            InputStream inputStream =classPathResource.getInputStream();
            byte[] bdata = FileCopyUtils.copyToByteArray(inputStream);
            String querySql = new String(bdata, StandardCharsets.UTF_8);

            ResultSet rs =  stat.executeQuery(querySql);

            log.info("ResultSet:"+rs);
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while (rs.next()){
                Map<String, Object> rowData = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
//                    if ("aliasName".equals(md.getColumnLabel(i))){
//                        rowData.put(md.getColumnLabel(i), SqlTableUtil.xhxToJavaStr(rs.getObject(i)==null?"":rs.getString(i)));
//                    }else{
                        rowData.put(md.getColumnLabel(i), rs.getObject(i)==null?"":rs.getString(i));
//                    }
                }
                list.add(rowData);
            }
        } catch (IOException | SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            try {
                if (stat != null){
                    stat.close();
                }
                if (conn != null){
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

        return list;
    }

    @Override
    public List<Map<String,Object>> selectUserAttribute(String tenantId){
        List<Map<String,Object>> list = new ArrayList<>();
        Connection conn = null;
        Statement stat = null;

        String url = paramConfig.getUrl();
        String username = paramConfig.getUsername();
        String password = paramConfig.getPassword();

        try {
            //连接数据库
            Class.forName(paramConfig.getDriverClassName());
            //测试url中是否包含useSSL字段，没有则添加设该字段且禁用
            conn = DriverManager.getConnection(url, username, password);
            stat = conn.createStatement();

            ClassPathResource classPathResource = new ClassPathResource("sql/users_attr_select_mysql.sql");

            InputStream inputStream =classPathResource.getInputStream();
            byte[] bdata = FileCopyUtils.copyToByteArray(inputStream);
            String querySql = new String(bdata, StandardCharsets.UTF_8);
            if (StringUtil.isNotEmpty(tenantId)){
                querySql += " and lessee_id ='"+tenantId+"'";
            }

            ResultSet rs =  stat.executeQuery(querySql);

            log.info("ResultSet:"+rs);
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while (rs.next()){
                Map<String, Object> rowData = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
//                    if ("aliasName".equals(md.getColumnLabel(i))){
//                        rowData.put(md.getColumnLabel(i), SqlTableUtil.xhxToJavaStr(rs.getObject(i)==null?"":rs.getString(i)));
//                    }else{
                    rowData.put(md.getColumnLabel(i), rs.getObject(i)==null?"":rs.getString(i));
//                    }
                }
                list.add(rowData);
            }
        } catch (IOException | SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            try {
                if (stat != null){
                    stat.close();
                }
                if (conn != null){
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

        return list;
    }

}
